<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class Cc_model_b extends CI_Model {

    public function __construct() {
        parent::__construct();
    }

    public function getDataMaterialXTest() {
        $query = "select m.m_material_id, 
                        em.e_nombre,
                        d.d_nombre,
                    mo.m_nombre,
                    sm.sm_nombre,
                    m.m_titulo,
                    pregs.n_preguntas 
                from empresa em
                join division d using (e_empresa_id)
                JOIN modulo mo using (d_division_id)
                JOIN sub_modulo sm using(m_modulo_id)
                JOIN material m using(sm_sub_modulo_id)
                join (
                        select m_material_id , count(*) n_preguntas 
                        from pregunta
                        group by m_material_id
                ) pregs using (m_material_id)";
        $resultado = $this->db->query($query);
        $out = $resultado->result();
        $resultado->next_result();
        $resultado->free_result();
        return $out;
    }

    /**
     * @return Object
     * Atributos:
     *  e_empresa_id   
     *  e_nombre
     *  nMateriales   
     *  totalTest  
     *  nTestDone     
     */
    public function dataGraphCom() {
        $query = "select em.e_empresa_id ,
                    em.e_nombre,
                    count(*) nMateriales,
                    x.nEmpleados,
                    (count(*)*x.nEmpleados) totalTest,
                    y.nTestDone 
                from empresa em
                join division d using (e_empresa_id)
                JOIN modulo mo using (d_division_id)
                JOIN sub_modulo sm using(m_modulo_id)
                JOIN material m using(sm_sub_modulo_id)
                JOIN (select d_division_id ,count(*) nEmpleados from empleado e
                                join division d using (d_division_id)
                                group by d.e_empresa_id
                ) x using (d_division_id) 
                JOIN (select d.e_empresa_id,count(*) nTestDone from empleado e
                                join cursos_tomados ct using (e_empleado_id)
                        join division d using(d_division_id)
                        group by d.e_empresa_id
                ) y using (e_empresa_id)
                group by em.e_empresa_id";
        $resultado = $this->db->query($query);
        $out = $resultado->result();
        $resultado->next_result();
        $resultado->free_result();
        return $out;
    }
    /**
     * 
     * @param type $captcha_time
     * @param type $ip_address
     * @param type $word
     */
    function insertCaptcha($captcha_time, $ip_address, $word) {
        $query = "insert into captcha(captcha_id,captcha_time,ip_address,word)
                values(NULL,?,?,?)";
        $this->db->query($query, array($captcha_time, $ip_address, $word));
    }
    /**
     * 
     * @param type $ip_address
     */
    function deleteCaptcha($ip_address) {
        $this->db->query("delete from captcha where ip_address = '$ip_address'");
    }
    
    /**
    * @param type $binds
    * @return boolean
    */
    function captchaExist($binds) {
        $sql = "select count(*) as count from captcha where word = ? and ip_address = ? and captcha_time > ?";
        $query = $this->db->query($sql, $binds);
        $row = $query->row();

        if ($row->count == 0)
            return FALSE;
        else
            return TRUE;
    }
    /**
     * 
     * @param type $l_ip
     * @param type $l_nombre
     */
    public function regAcceso($l_ip,$l_nombre){
        $query = "insert into log_accesos(l_nombre,l_fecha,l_hora,l_ip) values (?,date(NOW()),time(NOW()),?)";
        $this->db->query($query, array($l_nombre, $l_ip));
    }

}
